package com.nuo.jdbc.h2.init;

import java.io.BufferedReader;
import java.io.IOException;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.net.URL;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.List;

import org.apache.commons.dbutils.DbUtils;

import com.nuo.hibernate.entity.User;
import com.nuo.jdbc.ConnectionManager;
import com.nuo.jdbc.UserProvide;


/**
 * spring 有jdbc的初始化标签，才能所谓的初始化数据库，就是连接数据库然后执行sql语句。
 * 所以这个H2init类就是执行指定的sql文件，来进行所谓的初始化。
 * @author taoyf
 */
public class H2Init {
	public static void main(String[] args) throws IOException{
		InputStream is = H2Init.class.getResourceAsStream("/sql/h2/schema.sql");
		StringBuilder sb = new StringBuilder();
		
		BufferedReader br = new BufferedReader(new InputStreamReader(is));
		
		String line = null;
		do{
			try {
				line = br.readLine();
				if(line != null){
					sb.append(line);
				}
			} catch (IOException e) {
				System.out.println("读取文件失败");
				throw e;
			}
		}
		while(line != null);
		
		H2Init h2 = new H2Init();
		try {
			h2.init(sb.toString());
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		
		UserProvide provide = new UserProvide();
		User user1 = new User();
		user1.setName("taoyf");
		user1.setUpdateDate(new Timestamp(System.currentTimeMillis()));
		provide.saveOrUpdate(user1);
		
		System.out.println(provide.getAllUser());
		
		User user2 = new User();
		user2.setName("huanhuan");
		user2.setUpdateDate(new Timestamp(System.currentTimeMillis()));
		provide.saveOrUpdate(user2);
		
		System.out.println(provide.getAllUser());		
		
		User user3 = new User();
		user3.setName("tiancai");
		user3.setUpdateDate(new Timestamp(System.currentTimeMillis()));
		provide.saveOrUpdate(user3);
		
		
		
		List<User> list = provide.getAllUser();
		System.out.println(list);
		
		if(list.size() > 0){
			User user4 = list.get(list.size() - 1);
			user4.setName("tiancai2");
			provide.saveOrUpdate(user4);
		}
		
		System.out.println(provide.getAllUser());		
		
	}
	
	
	public void init(String sql) throws Exception{
		Connection con = null;
		Statement pstmt = null;
		ResultSet rs = null;

		try {
			con = ConnectionManager.getConnection();
			con.setAutoCommit(false);
			
			pstmt = con.createStatement();
			pstmt.addBatch(sql);
			pstmt.executeBatch();
			con.commit();
		} catch (SQLException e) {
			
			if(con != null){
				con.rollback();
			}
			e.printStackTrace();
		} finally{
			DbUtils.closeQuietly(con, pstmt, rs);
		}
		/*
		
		sql1 = "insert into ss_task (id, title, description, user_id) values(1, 'Study PlayFramework 2.0','http://www.playframework.org/', 2)";
		try {
			con = ConnectionManager.getConnection();
			pstmt = con.createStatement();
			pstmt.executeUpdate(sql1);
		} catch (SQLException e) {
			e.printStackTrace();
		} finally{
			DbUtils.closeQuietly(con, pstmt, rs);
		}
		
		sql1 = "select * from ss_task";
		List result = new ArrayList();
		try {
			con = ConnectionManager.getConnection();
			pstmt = con.createStatement();
			rs = pstmt.executeQuery(sql1);
			
			ResultSetMetaData md = rs.getMetaData();
			
			int totalCol = md.getColumnCount();
			List list = new ArrayList();
			for(int i = 0; i < totalCol; i++){
				list.add(i, md.getColumnLabel(i + 1).toLowerCase());
			}
			result.add(0, list);
			
			int num = 0;
			while(rs.next()){
				List temp = new ArrayList();
				for(int i = 0; i < totalCol; i++){
					Object obj = rs.getObject(i + 1);
					temp.add(i, obj);
				}
				result.add(temp);
			}
			System.out.println(result);
		} catch (SQLException e) {
			e.printStackTrace();
		} finally{
			DbUtils.closeQuietly(con, pstmt, rs);
		}
		*/
		
		System.out.println(getAllTables());
		System.out.println(getAllTables());
		System.out.println(getAllTables());
		
	}
	
	
	
	/**
	 * 得到某个表空间（数据库）的全部表
	 * @return
	 * @throws Exception 
	 */
	public List<String> getAllTables() throws Exception{
		try {
			Connection con = ConnectionManager.getConnectionFromPool();
			con = ConnectionManager.getConnectionFromPool();
			con = ConnectionManager.getConnectionFromPool();
			
			DatabaseMetaData dbmd = con.getMetaData();
			
			ConnectionManager.printDataSourceStats();
			
			ResultSet set = dbmd.getTables(null, null, null, new String[]{"TABLE"});
			List<String> all_Tables = new ArrayList<String>();
			while(set.next()){
				all_Tables.add(set.getString(3));
			}
			
			con.close();
			
			return all_Tables;
		} catch (SQLException e) {
			e.printStackTrace();
			return new ArrayList<String>();
		}
		
	}
}
